﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace WebMvc.App.Controllers
{
    public class SqliteTrans:Controller
    {
        public void update(string name, string pwd)
        {
            var con = Models.Common.OpenConnection();
            var trans = con.BeginTransaction();
            int count = 0;
            try
            {
                count = trans.Update("update users set name=@name,pwd=@pwd where id=1");
                trans.Commit();
            }
            catch
            {
                trans.Rollback();
            }
            con.Close();
            Write(count);
        }
        public void insert(string name, string pwd)
        {
            var con = Models.Common.OpenConnection();
            var trans = con.BeginTransaction();
            int id = 0;
            try
            {
                id = trans.Insert("insert into users(name,pwd) values(@name,@pwd)");
                trans.Commit();
            }
            catch
            {
                trans.Rollback();
            }
            con.Close();
            Write(id);
        }
        public void delete(int id)
        {
            var con = Models.Common.OpenConnection();
            var trans = con.BeginTransaction();
            int count = 0;
            try
            {
                count = trans.Delete("delete from users where id=@id");
                trans.Commit();
            }
            catch
            {
                trans.Rollback();
            }
            con.Close();
            Write(count);
        }
        public void page(int p)
        {
            var con = Models.Common.OpenConnection();
            var trans = con.BeginTransaction();
            NFinal.Page page = new NFinal.Page(p, 2);
            try
            {
                var users = trans.Page("select * from users", page);
                AjaxReturn(users);
                trans.Commit();
            }
            catch
            {
                trans.Rollback();
            }
            con.Close();
        }
        public void queryAll()
        {
            var con = Models.Common.OpenConnection();
            var trans = con.BeginTransaction();
            try
            {
                var users = trans.QueryAll("select * from users");
                AjaxReturn(users);
                trans.Rollback();
            }
            catch
            {
                trans.Rollback();
            }
            con.Close();
        }
        public void queryObject()
        {
            var con = Models.Common.OpenConnection();
            var trans = con.BeginTransaction();
            try
            {
                var count = trans.QueryObject("select count(*) from users").ToInt32();
                trans.Commit();
                Write(count);
            }
            catch
            {
                trans.Rollback();
            }
            con.Close();
        }
        public void queryRandom()
        {
            var con = Models.Common.OpenConnection();
            var trans = con.BeginTransaction();
            try
            {
                var users = trans.QueryRandom("select * from users", 3);
                trans.Commit();
                AjaxReturn(users);
            }
            catch
            {
                trans.Rollback();
            }
            con.Close();
        }
        public void queryRow(int id)
        {
            var con = Models.Common.OpenConnection();
            var trans = con.BeginTransaction();
            try
            {
                var user = trans.QueryRow("select * from users where id=@id");
                trans.Commit();
                AjaxReturn(user);
            }
            catch
            {
                trans.Rollback();
            }
            con.Close();
        }
        public void queryTop(int top)
        {
            var con = Models.Common.OpenConnection();
            var trans = con.BeginTransaction();
            try
            {
                var users = trans.QueryTop("select * from users", top);
                AjaxReturn(users);
                trans.Commit();
            }
            catch
            {
                trans.Rollback();
            }
            con.Close();
        }
    }
}